﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using WAF.Data;

namespace EntityTools.Logic.Interface
{
    class MysqlDbInfo : IDatabaseInfo
    {
        private string databaseName;
        private string server;
        private string username;
        private string password;

        public MysqlDbInfo(string servername, string dbname, string uname, string pwd)
        {
            databaseName = dbname;
            server = servername;
            username = uname;
            password = pwd;

            DbAccess.DbType = DbAccess.DatabaseType.MYSQL;
            DbAccess.ConnectionString = "server=" + server + ";database=" + databaseName + ";user id=" + username + ";password=" + password + ";";
        }

        public List<string> GetTablesFromDb(string dbName)
        {
            databaseName = dbName;
            List<string> tables = new List<string>();

            //获取表
            DataTable tb = DbAccess.ExeDataSet("show tables").Tables[0];

            foreach (DataRow row in tb.Rows)
            {
                string firstWord = row[0].ToString().Substring(0, 1).ToUpper(); //首字母大写

                tables.Add(firstWord + row[0].ToString().Remove(0,1));
            }

            return tables;
        }

        public List<DbFieldInfo> GetFieldsFromTable(string tableName)
        {
            List<DbFieldInfo> fieldInfos = new List<DbFieldInfo>();
            string constring = "server=" + server + ";database=information_schema" + ";user id=" + username + ";password=" + password + ";";
            
            string sql = "select column_name, ordinal_position, is_nullable, data_type, character_maximum_length, column_key, extra, column_comment " +
                         "from columns where table_schema='" + databaseName + "' and table_name='" + tableName + "'";

            string foreignSql = "select constraint_schema, constraint_name, table_name, column_name, referenced_table_name, referenced_column_name " +
                                "from key_column_usage where constraint_schema = '" + databaseName + "' and table_name = '" + tableName + "' and constraint_name != 'PRIMARY'";

            DbAccess db = new DbAccess(DbAccess.DatabaseType.MYSQL, constring);
            db.Open();
            DataTable tb = db.ExecuteDataSet(sql).Tables[0];
            DataTable foreignTb = db.ExecuteDataSet(foreignSql).Tables[0];
            db.Close();

            int i = 0;
            string sharptype, dbtype;
            string isForeignKey, relTable, relIdField, relNameField;

            foreach (DataRow row in tb.Rows)
            {
                DbFieldInfo fInfo = new DbFieldInfo();
                getDbTypeString(row["data_type"].ToString().Trim(), out sharptype, out dbtype);
                getForeignKey(foreignTb, tableName, row["column_name"].ToString(), out isForeignKey, out relTable, out relIdField, out relNameField);

                fInfo.Name = row["column_name"].ToString();
                fInfo.FieldType = sharptype;
                fInfo.Sequence = (i++).ToString();
                fInfo.IsPrimeKey = row["column_key"].ToString().ToUpper() == "PRI" ? "true" : "false";
                fInfo.IsUnique = fInfo.IsPrimeKey;
                fInfo.IsNotNull = row["is_nullable"].ToString().ToUpper() == "NO" ? "true" : "false";
                fInfo.IsForeignKey = isForeignKey;
                fInfo.DatabaseType = dbtype;
                fInfo.Size = DBNull.Value.Equals(row["character_maximum_length"]) ? "0" : row["character_maximum_length"].ToString().Trim();
                fInfo.Description = DBNull.Value.Equals(row["column_comment"]) ? "" : row["column_comment"].ToString().Trim();
                fInfo.RelTable = relTable;
                fInfo.RelIdField = relIdField;
                fInfo.RelNameField = relNameField;

                fieldInfos.Add(fInfo);
            }

            return fieldInfos;
        }

        //获取外键信息
        private void getForeignKey(DataTable tb, string tbName, string fieldName, out string isForeignKey, out string RelTable, out string RelIdField, out string RelNameField)
        {
            isForeignKey = "false";
            RelTable = "";
            RelIdField = "";
            RelNameField = "";

            foreach (DataRow row in tb.Rows)
            {
                if (row["table_name"].ToString() == tbName && row["column_name"].ToString() == fieldName)
                {
                    isForeignKey = "true";
                    RelTable = row["referenced_table_name"].ToString();
                    RelIdField = row["referenced_column_name"].ToString();
                    RelNameField = row["constraint_name"].ToString();
                }
            }
        }

        //获取字段类型
        private void getDbTypeString(string type, out string sharptype, out string dbtype)
        {
            sharptype = "";
            dbtype = "";

            switch (type.ToUpper())
            {
                case "INT":
                    sharptype = "int";
                    dbtype = "DbType.Int32";
                    break;
                case "INTEGER":
                    sharptype = "long";
                    dbtype = "DbType.Int64";
                    break;
                case "FLOAT":
                    sharptype = "float";
                    dbtype = "DbType.Double";
                    break;
                case "DOUBLE":
                    sharptype = "double";
                    dbtype = "DbType.Double";
                    break;
                case "VARCHAR":
                    sharptype = "string";
                    dbtype = "DbType.String";
                    break;
                default:
                    sharptype = "string";
                    dbtype = "DbType.String";
                    break;
            }
        }
    }
}
